from django.views import View
from django.http.response import JsonResponse
from . import models
import json
from django.db.models import F,Q
from django.db.models import Max,Min,Avg,Count,Sum

class Student(View):
    ###获取数据
    def get(self,request):
        #获取Student表所有数据
        # student_list = []
        # obj_list = models.Student.objects.all()
        # print(type(student_list))     #<class 'django.db.models.query.QuerySet'>
        # for obj in obj_list:
        #     student_list.append({
        #         "id":obj.id,
        #         "name":obj.name,
        #         "age":obj.age,
        #         "sex":obj.get_sex_display(),
        #         "sid":obj.sid,
        #         "introduction":obj.introduction,
        #         "status":obj.get_status_display(),
        #         "className":obj.className,
        #         "created_time":obj.created_time,
        #         "updated_time":obj.updated_time
        #     })
        # print(student_list)

        #获取需要查询信息的学生的姓名
        name = request.GET.get('name')
        #根据姓名过滤表数据
        obj_list = models.Student.objects.filter(name = name)

        if obj_list:
            #空列表student_list存储根据姓名查询到的个人数据
            student_list = []
            #遍历obj_list并添加数据到student_list列表
            for obj in obj_list:
                student_list.append({
                    "sid":obj.sid,
                    "name":obj.name,
                    "age":obj.age,
                    "sex":obj.get_sex_display(),
                    "class":obj.className,
                    "introduction":obj.introduction,
                    "status":obj.get_status_display()
                })
            #返回成功的json响应
            return JsonResponse(student_list,safe=False,json_dumps_params={'ensure_ascii': False})
        else:
            #返回失败的json响应
            return JsonResponse({},status=404)
        
    ###添加数据
    def post(self,request):
         #添加单个数据
         request = json.loads(request.body)
         info = request['info']
         print(info)
         student = models.Student.objects.create(
             sid = info['sid'],
             name = info['name'],
             age = info['age'],
             sex = info['sex'],
             className = info['className'],
             introduction = info['introduction'],
             status = info['status']
         )
         response = {
             "id":student.id
         }
         return JsonResponse(response,json_dumps_params={'ensure_ascii': False})
    
        # 添加多个数据
        # request_data = json.loads(request.body)
        # request_list = request_data.get('info').get('list')
        # print(request_list)
        # response = []
        # student_list = []
        # for item in request_list:
        #     student = models.Student(
        #         sid = item.get('sid'),
        #         name = item.get('name'),
        #         age = item.get('age'),
        #         sex = item.get('sex'),
        #         className = item.get('className'),
        #         introduction = item.get('introduction'),
        #         status = item.get('status')
        #     )
        #     student_list.append(student)
        #     response.append(student.sid)
        # models.Student.objects.bulk_create(student_list)
        # return JsonResponse(response,safe=False)

    ###删除数据
    def delete(self,reuqest):
        #删除单个数据
        request = json.loads(reuqest.body)
        del_sid = request.get('sid')
        del_data = models.Student.objects.get(sid = del_sid)
        response = {
            "id":del_data.id
        }
        del_data.delete()
        return JsonResponse(response,json_dumps_params={'ensure_ascii': False})
        #删除多条数据
        # request_data = json.loads(reuqest.body)
        # response = []
        # del_list = request_data.get('list')
        # students = models.Student.objects.filter(sid__in = del_list)
        # for student in students:
        #     response.append(student.id)
        # students.delete()
        # return JsonResponse(response,safe=False)

    ###更新数据
    def put(self,request):
        request = json.loads(request.body)
        update_sid = request['sid']
        status = request['status']
        student = models.Student.objects.get(sid = update_sid)
        student.status = status
        student.save()
        return JsonResponse({},json_dumps_params={'ensure_ascii': False})

    ###########################################

    ###查询班级为3-01的学生的姓名、性别和学号
    def get01(self,request):
        #exact
        students = models.Student.objects.filter(className__exact = '3-01').all()
        response = []
        for student in students:
            response.append({
                "sid":student.sid,
                "name":student.name,
                "sex":student.sex
            })
        return JsonResponse(response,safe=False)

    ###查询三年级的学生的姓名和班级
    def get02(self,request):
        #startswith
        students = models.Student.objects.filter(className__startswith = '3').all()
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "class":student.className
            })
        return JsonResponse(response,safe=False)

    ###查询个人介绍包含‘之’字的学生的姓名和个人介绍
    def get03(self,request):
        #contains
        students = models.Student.objects.filter(introduction__contains = '之').all()
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "introduction":student.introduction
            })
        return JsonResponse(response,safe=False)
    
    ###查询个人介绍为空的学生的姓名和个人介绍
    def get04(self,request):
        #isnull
        students = models.Student.objects.filter(introduction__isnull = True).all()
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "introduction":student.introduction
            })
        return JsonResponse(response,safe=False)
    
    ###查询年龄大于18的学生的姓名和年龄
    def get05(self,request):
        #gt
        students = models.Student.objects.filter(age__gt = 18).all()
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "age":student.age
            })
        return JsonResponse(response,safe=False)
    
    ###查询年龄不大于20的学生的姓名和年龄
    def get06(self,request):
        #lte
        students = models.Student.objects.filter(age__lte = 20).all()
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "age":student.age
            })
        return JsonResponse(response,safe=False)
    
    ###查询学生状态为1或2的学生的姓名和状态
    def get07(self,request):
        #in
        students = models.Student.objects.filter(status__in = [1,2]).all()
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "status":student.status
            })
        return JsonResponse(response,safe=False)
    
    ###查询年龄为20至25的学生的姓名和年龄
    def get08(self,request):
        #range
        students = models.Student.objects.filter(age__range = (20,25)).all()
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "age":student.age
            })
        return JsonResponse(response,safe=False)
    
    ###查询未修改过信息的同学的学号和姓名(创建时间等于更新时间)
    def get09(self,request):
        #F对象
        students = models.Student.objects.filter(created_time = F('updated_time')).all()    #判断两个字段的相等
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "sid":student.sid
            })
        return JsonResponse(response,safe=False)
    
    ###查询一年级的女生或二年级的男生的姓名、班级和性别
    def get10(self,request):
        #Q对象
        students = models.Student.objects.filter(Q(className__startswith = '1',sex = 2) | Q(sex = 1,className__startswith = '2')).all()     #实现复杂的查询逻辑
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "sex":student.get_sex_display(),
                "class":student.className
            })
        return JsonResponse(response,safe=False)
    
    ###查询一、二年级的同学的姓名和班级
    def get11(self,request):
        #exclude
        students = models.Student.objects.exclude(className__startswith = '3').all()     
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "class":student.className
            })
        return JsonResponse(response,safe=False)
    
    ###查询一年级的同学的姓名、性别和年龄，并按性别升序、年龄降序来排序
    def get12(self,request):
        #order_by
        students = models.Student.objects.filter(className__startswith = '1').order_by('sex','-age').all()      #在字段前加负号表示降序，不加表示升序
        response = []
        for student in students:
            response.append({
                "name":student.name,
                "sex":student.get_sex_display(),
                "age":student.age
            })
        return JsonResponse(response,safe=False)
    
    ###查询已毕业的同学的姓名，只返回前五条数据
    def get13(self,request):
        #切片
        students = models.Student.objects.filter(status = 2)[0:5]
        response = []
        for student in students:
            response.append({
                "id":student.id,
                "name":student.name
            })
        return JsonResponse(response,safe=False)
    
    ###优化查询结果
    def getQuery(self,request):
        #返回的查询结果集中是模型对象(对象列表)
        students1 = models.Student.objects.all()[0:5]
        #返回的查询结果集中是指定字段构成的字典(字典列表)
        students2 = models.Student.objects.all().values('sid','name','sex','age')[0:5]
        #返回的查询结果集中是指定字段构成的元组(元组列表)
        students3 = models.Student.objects.all().values_list('sid','name','sex','age')[0:5]
        print('@@@',students1)
        print('###',students2)
        print('&&&',students3)
        return JsonResponse({})

    #聚合函数  aggregate
    def get14(self,request):
        #一年级所有学生的年龄总和
        sum_age = models.Student.objects.filter(className__startswith = '1').aggregate(all_age = Sum('age'))
        #一年级所有学生的年龄平均数
        avg_age = models.Student.objects.filter(className__startswith = '1').aggregate(avg_age = Avg('age'))
        #一年级五班所有同学中年龄最大值
        max_age = models.Student.objects.filter(className__exact = '1-05').aggregate(max_age = Max('age'))
        #一年级五班所有同学中年龄最小值
        min_age = models.Student.objects.filter(className__exact = '1-05').aggregate(max_age = Min('age'))
        #一年级五班所有同学的数量
        count_students = models.Student.objects.filter(className__exact = '1-05').count()

        response = {}
        response['list'] = [sum_age,avg_age,max_age,min_age,count_students]
        return JsonResponse(response)
    
    #分组查询  values annotate 
    def get15(self,request):
        #查询每个班级的学生的总人数
        res1 = models.Student.objects.values('className').annotate(students_count = Count('sid')).order_by('className')
        print('###',res1)
        #查询三年级每个班级的学生的总人数
        res2 = models.Student.objects.filter(className__startswith = '3').values('className').annotate(students_count = Count('sid')).order_by('className')
        print('###',res2)
        #查询三年级每个班级的平均年龄(大于20)
        res3 = models.Student.objects.filter(className__startswith = '3').values('className').annotate(avg_age = Avg('age')).filter(avg_age__gt = 20)
        print('###',res3)
        return JsonResponse({})
    
class Teacher(View):
    ###获取数据
    def get(self,request):
        name = request.GET.get('name')
        list = []
        main_object = models.Teacher_main.objects.filter(name = name)
        for obj in main_object:
            list.append({
                #主表信息
                "name":obj.name,
                "sex":obj.get_sex_display(),
                "age":obj.age,
                "tid":obj.tid,
                #从表信息
                "background":obj.profile.get_background_display(),
                "experience":obj.profile.experience,
                "mail":obj.profile.mail,
                "subjects":obj.profile.subjects,
                "tel":obj.profile.tel
            })
        return JsonResponse(list,safe=False,json_dumps_params={'ensure_ascii': False})
    
    ###添加数据
    def post(self,request):
        request = json.loads(request.body)
        t = models.Teacher_main.objects.create(
            name = request['name'],
            sex = request['sex'],
            age = request['age'],
            tid = request['tid']
        )
        models.Teacher_secondary.objects.create(
            teacher = t,
            background = request['background'],
            experience = request['experience'],
            mail = request['mail'],
            subjects = request['subjects'],
            tel = request['tel'],
        )
        response = {
            "id":t.id,
        }
        return JsonResponse(response,json_dumps_params={'ensure_ascii': False})
    
    ###修改数据
    def put(self,request):
        request = json.loads(request.body)

        first = models.Teacher_main.objects.filter(tid = request['tid']).first()
        secondary = models.Teacher_secondary.objects.filter(teacher__tid = request['tid']).first()

        first.name = request['name']
        first.sex = request['sex']
        first.age = request['age']

        secondary.experience = request['experience']
        secondary.mail = request['mail']
        secondary.subjects = request['subjects']
        secondary.tel = request['tel']
        secondary.background = request['background']

        first.save()

        secondary.save()

        return JsonResponse({},json_dumps_params={'ensure_ascii': False})
    
    ###删除数据
    def delete(self,request):
        request = json.loads(request.body)
        del_teacher = models.Teacher_main.objects.filter(tid = request['tid'])
        del_teacher.delete()
        response = {
            "tid":request['tid']
        }
        return JsonResponse(response,json_dumps_params={'ensure_ascii': False})